﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

using DTO;

namespace DAO
{
    public class NhanVienDAO
    {
        public List<NhanVienDTO> LayDSNV()
        {
            string sql = "SELECT * FROM NhanVien WHERE Deleted=0  ORDER BY SoTT";
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataReader reader = cmd.ExecuteReader();
            List<NhanVienDTO> danhsach = new List<NhanVienDTO>();
            while (reader.Read())
            {
                NhanVienDTO nvDTO = new NhanVienDTO();
                nvDTO.MaNV = reader.GetString(0);
                nvDTO.TenNV = reader.GetString(1);
                nvDTO.MaVaTen = nvDTO.MaNV + " - " + nvDTO.TenNV;
                nvDTO.DienThoai = reader.GetString(2);
                nvDTO.NgaySinh = reader.GetDateTime(3);
                nvDTO.GioiTinh = reader.GetInt32(4);
                nvDTO.MaBoPhan = reader.GetInt32(5);
                nvDTO.DiaChi = reader.GetString(6);
                nvDTO.Luong = reader.GetInt32(7);
                danhsach.Add(nvDTO);
            }
            conn.Close();
            return danhsach;
        }
        public bool KTmanv(string MaNV)
        {
            string sql = "SELECT COUNT(*) FROM NhanVien WHERE MaNV='" + MaNV + "'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                read.Read();
                int kq = read.GetInt32(0);
                conn.Close();
                if (kq == 0)
                    return true;
                return false;
            }

        }
        public bool Themnhanvien(NhanVienDTO nv)
        {
            string sql = "INSERT INTO NhanVien(MaNV, TenNV, DienThoai, NgaySinh,GioiTinh, MaBoPhan,DiaChi,Luong,Deleted)";
            sql += "VALUES('" + nv.MaNV + "',N'" + nv.TenNV + "', '" + nv.DienThoai + "', '" + nv.NgaySinh + "', " + nv.GioiTinh + "," + nv.MaBoPhan + ",N'" + nv.DiaChi + "'," + nv.Luong + ", 0)";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;
                return false;
            }
        }

        public NhanVienDTO Laynvtheoma(string MaNV)
        {
            string sql = "SELECT * FROM NhanVien WHERE MaNV = '" + MaNV + "'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                read.Read();
                NhanVienDTO nvDTO = new NhanVienDTO();
                nvDTO.MaNV = read.GetString(0);
                nvDTO.TenNV = read.GetString(1);
                nvDTO.MaVaTen = nvDTO.MaNV + " - " + nvDTO.TenNV;
                nvDTO.DienThoai = read.GetString(2);
                nvDTO.NgaySinh = read.GetDateTime(3);
                nvDTO.GioiTinh = read.GetInt32(4);
                nvDTO.MaBoPhan = read.GetInt32(5);
                nvDTO.DiaChi = read.GetString(6);
                nvDTO.Luong = read.GetInt32(7);
                conn.Close();
                return nvDTO;
            }

        }

        public bool Capnhatnv(NhanVienDTO nv)
        {
            string sql = "UPDATE NhanVien SET MaNV = '" + nv.MaNV + "', TenNV = N'" + nv.TenNV + "', ";
            sql += "DienThoai = '" + nv.DienThoai + "', NgaySinh = '" + nv.NgaySinh + "',GioiTinh = " + nv.GioiTinh + ",MaBoPhan = " + nv.MaBoPhan + ",DiaChi = N'" + nv.DiaChi + "',Luong = " + nv.Luong + " WHERE MaNV = '" + nv.MaNV + "'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;
                return false;
            }
        }
        public List<NhanVienDTO> Laynvtheoten(string TenNV)
        {
            string sql = "SELECT * FROM NhanVien WHERE TenNV like N'%" + TenNV + "%'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                List<NhanVienDTO> danhsach = new List<NhanVienDTO>();
                while (read.Read())
                {
                    NhanVienDTO nvDTO = new NhanVienDTO();
                    nvDTO.MaNV = read.GetString(0);
                    nvDTO.TenNV = read.GetString(1);
                    nvDTO.DienThoai = read.GetString(2);
                    nvDTO.NgaySinh = read.GetDateTime(3);
                    nvDTO.GioiTinh = read.GetInt32(4);
                    nvDTO.MaBoPhan = read.GetInt32(5);
                    nvDTO.DiaChi = read.GetString(6);
                    nvDTO.Luong = read.GetInt32(7);

                    danhsach.Add(nvDTO);
                }
                conn.Close();
                return danhsach;
            }
        }
        public List<NhanVienDTO> Laynvtheotenbp(string TenBoPhan)
        {
            string sql = "SELECT * FROM NhanVien NV,BoPhan BP WHERE BP.TenBoPhan like N'" + TenBoPhan + "' AND NV.MaBoPhan = BP.MaBoPhan ";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                List<NhanVienDTO> danhsach = new List<NhanVienDTO>();
                while (read.Read())
                {
                    NhanVienDTO nvDTO = new NhanVienDTO();
                    nvDTO.MaNV = read.GetString(0);
                    nvDTO.TenNV = read.GetString(1);
                    nvDTO.DienThoai = read.GetString(2);
                    nvDTO.NgaySinh = read.GetDateTime(3);
                    nvDTO.GioiTinh = read.GetInt32(4);
                    nvDTO.MaBoPhan = read.GetInt32(5);
                    nvDTO.DiaChi = read.GetString(6);
                    nvDTO.Luong = read.GetInt32(7);
                    danhsach.Add(nvDTO);
                }
                conn.Close();
                return danhsach;
            }
        }
        public List<NhanVienDTO> Laynvtheotenvatenbp(string Tennv, string TenBoPhan)
        {
            string sql = "SELECT * FROM NhanVien NV,BoPhan BP WHERE NV.TenNV like N'%" + Tennv + "%'AND BP.TenBoPhan like N'"+ TenBoPhan + "' AND NV.MaBoPhan = BP.MaBoPhan ";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                List<NhanVienDTO> danhsach = new List<NhanVienDTO>();
                while (read.Read())
                {
                    NhanVienDTO nvDTO = new NhanVienDTO();
                    nvDTO.MaNV = read.GetString(0);
                    nvDTO.TenNV = read.GetString(1);
                    nvDTO.DienThoai = read.GetString(2);
                    nvDTO.NgaySinh = read.GetDateTime(3);
                    nvDTO.GioiTinh = read.GetInt32(4);
                    nvDTO.MaBoPhan = read.GetInt32(5);
                    nvDTO.DiaChi = read.GetString(6);
                    nvDTO.Luong = read.GetInt32(7);
                    danhsach.Add(nvDTO);
                }
                conn.Close();
                return danhsach;
            }
        }
        public bool Xoanhanvien(string MaNV)
        {
            string sql = "UPDATE NhanVien SET Deleted = 1 WHERE MaNV = N'" + MaNV + "'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;
                return false;
            }
        }
    }
}
